/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import javax.sql.DataSource;
import limousine.vo.Booking;
import org.apache.log4j.Logger;

/**
 *
 * @author Tailer
 */
public class BookingService implements Service {

    private DataSource ds = null;
    private int limit = 20;
    private int timeout = 1;
    private Logger log = Logger.getLogger(this.getClass());

    public synchronized Booking getByBookingId(String bookingId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT BOOKING_ID,");
        sql.append("IFNULL(CUSTOMER_ID, '-') AS CUSTOMER_ID, ");
        sql.append("IFNULL(CREATE_BY, '-') AS CREATE_BY, ");
        sql.append("IFNULL(CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        sql.append("IFNULL(UPDATE_BY, '-') AS UPDATE_BY, ");
        sql.append("IFNULL(UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
        sql.append("IFNULL(STATUS, '-') AS STATUS, ");
        sql.append("IFNULL(RLOCK, '-') AS RLOCK ");
        sql.append("FROM booking WHERE BOOKING_ID = '");
        sql.append(bookingId.trim());
        sql.append("'");
        log.info("SQL:["+ sql.toString()+"]");
        Booking o = null;
        Connection conn = null;
        boolean chkLock = false;
        try {
            conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Booking();
                o.setBookingId(rs.getString("BOOKING_ID"));
                o.setCustomerId(rs.getString("CUSTOMER_ID"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new java.util.Date(rs.getTimestamp("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new java.util.Date(rs.getTimestamp("UPDATE_DATE").getTime()));
                o.setStatus(rs.getInt("STATUS"));
                o.setLock(rs.getString("RLOCK"));
                o.setTimeout(checkTimeout(o.getCreateDate()));
                if (o.getLock().equals("N")) {
                    chkLock = true;
                }
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (Exception e) {
            log.debug("-SQL Error-", e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                    if (chkLock == true) {
                        log.info("----- get and lock -----");
                        lock(o.getUpdateBy(), bookingId);
                    }
                } catch (Exception ex) {
                    log.debug("-Close Connection Error.-", ex);
                }
            }
        }
        return o;
    }

    public synchronized Booking getByBookingId(String uid, String bookingId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT BOOKING_ID,");
        sql.append("IFNULL(CUSTOMER_ID, '-') AS CUSTOMER_ID, ");
        sql.append("IFNULL(CREATE_BY, '-') AS CREATE_BY, ");
        sql.append("IFNULL(CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        sql.append("IFNULL(UPDATE_BY, '-') AS UPDATE_BY, ");
        sql.append("IFNULL(UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
        sql.append("IFNULL(STATUS, '-') AS STATUS, ");
        sql.append("IFNULL(RLOCK, '-') AS RLOCK ");
        sql.append("FROM booking WHERE BOOKING_ID = '");
        sql.append(bookingId.trim());
        sql.append("'");
        Booking o = null;
        Connection conn = null;
        boolean chkLock = false;
        try {
            conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Booking();
                o.setBookingId(rs.getString("BOOKING_ID"));
                o.setCustomerId(rs.getString("CUSTOMER_ID"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new java.util.Date(rs.getTimestamp("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new java.util.Date(rs.getTimestamp("UPDATE_DATE").getTime()));
                o.setStatus(rs.getInt("STATUS"));
                o.setLock(rs.getString("RLOCK"));
                o.setTimeout(checkTimeout(o.getCreateDate()));
                if (o.getLock().equals("N")) {
                    chkLock = true;
                }
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (Exception e) {
            log.debug("-SQL Error-", e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                    if (chkLock == true) {
                        log.info("----- get and lock -----");
                        lock(uid, bookingId);
                    }
                } catch (Exception ex) {
                    log.debug("-Close Connection Error-", ex);
                }
            }
        }
        return o;
    }

    public boolean save(Object obj) {
        Booking o = (Booking) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO booking(BOOKING_ID,CUSTOMER_ID,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE,STATUS) ");
        sql.append("VALUES (?,?,?,NOW(),?,NOW(),2)");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getBookingId());
            stmt.setString(2, o.getCustomerId());
            stmt.setString(3, o.getCreateBy());
            stmt.setString(4, o.getUpdateBy());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                    unLock(o.getUpdateBy(), o.getBookingId());
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    public boolean update(Object obj) {
        Booking o = (Booking) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE booking ");
        sql.append("SET CUSTOMER_ID=?,UPDATE_BY=?,UPDATE_DATE=NOW(),STATUS=?");
        sql.append(" WHERE BOOKING_ID=?");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getCustomerId());
            stmt.setString(2, o.getUpdateBy());
            stmt.setInt(3, o.getStatus());
            stmt.setString(4, o.getBookingId());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                    unLock(o.getUpdateBy(), o.getBookingId());
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    private boolean deleteByBookingId(String bookingId) {
        StringBuilder sql = new StringBuilder();
        sql.append("delete from booking where BOOKING_ID = '");
        sql.append(bookingId.trim());
        sql.append("'");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public void setTimeout(int timeout) {
        this.timeout = timeout;
    }

    public boolean remove(Object obj) {
        return this.deleteByBookingId((String) obj);
    }

    public Object findById(String id) {
        return this.getByBookingId(id);
    }

    public Object findById(String uid, String id) {
        return this.getByBookingId(uid, id);
    }

    public boolean removeAll(String[] ids) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List search(int offset, Object[] certirial) {
        List list = new ArrayList();
        String customerId = (String) certirial[0];
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT BOOKING_ID,");
        sql.append("IFNULL(CUSTOMER_ID, '-') AS CUSTOMER_ID, ");
        sql.append("IFNULL(CREATE_BY, '-') AS CREATE_BY, ");
        sql.append("IFNULL(CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        sql.append("IFNULL(UPDATE_BY, '-') AS UPDATE_BY, ");
        sql.append("IFNULL(UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
        sql.append("IFNULL(STATUS, '-') AS STATUS ");
        sql.append("FROM booking WHERE CUSTOMER_ID = '");
        sql.append(customerId.trim());
        sql.append("' ORDER BY UPDATE_DATE DESC");
        sql.append(" LIMIT ");
        sql.append(limit);
        sql.append(" OFFSET ");
        sql.append(offset);
        Booking o = null;
        Connection conn = null;
        try {
            conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Booking();
                o.setBookingId(rs.getString("BOOKING_ID"));
                o.setCustomerId(rs.getString("CUSTOMER_ID"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
                o.setStatus(rs.getInt("STATUS"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return list;
    }

    public int searchCount(int offset, Object[] certirial) {
        int count = 0;
        String customerId = (String) certirial[0];
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) FROM booking WHERE CUSTOMER_ID = '");
        sql.append(customerId.trim());
        sql.append("'");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return count;
    }

    private boolean lock(String uid, String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE booking SET RLOCK = 'Y', UPDATE_BY = ? WHERE BOOKING_ID = ? AND STATUS in(1,2)");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, uid);
            stmt.setString(2, id);
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return true;
    }

    private String checkTimeout(Date createDate) {
        Calendar c = Calendar.getInstance(Locale.US);
        long now = c.getTimeInMillis();
        c.setTime(createDate);
        c.set(Calendar.HOUR, c.get(Calendar.HOUR) + timeout);
        long before = c.getTimeInMillis();
        return (now >= before) ? "Y" : "N";
    }

    private boolean unLock(String uid, String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE booking SET RLOCK = 'N' WHERE BOOKING_ID = ? AND UPDATE_BY = ?");
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, id);
            stmt.setString(2, uid);
            stmt.executeUpdate();
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return false;
    }

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }
    /*
    public static void main(String args[]) throws Exception {
    SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    Date date = sf.parse("2011-10-29 20:30:00");
    BookingService b = new BookingService();
    System.out.println("Check:"+b.checkTimeout(date));
    }*/
}
